/**
 * 
 */
package fr.greta.centreFormation.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

import fr.greta.centreFormation.bean.Administrateur;

/**
 * @author Betty Boop
 * 
 */
public class AdministrateurHome {

	public static int insertAdministrateur(Administrateur unAdministrateur) {

		String nomPersonne = unAdministrateur.getNom();
		String prenomPersonne = unAdministrateur.getPrenom();
		String mailPersonne = unAdministrateur.getMail();
		String tel1Personne = unAdministrateur.getNumTel1();
		String tel2Personne = unAdministrateur.getNumtel2();
		String adressePersonne = unAdministrateur.getAdresse();
		String villePersonne = unAdministrateur.getVille();
		int codePostal = unAdministrateur.getCodePostale();
		String login = unAdministrateur.getLogin();
		String password = unAdministrateur.getPassword();
		String posteAdmin = unAdministrateur.getFonction();
		Date dateNaissancejava = unAdministrateur.getDateNaissance();
		java.sql.Date dateNaissanceSql = new java.sql.Date(
				dateNaissancejava.getTime());

		String sql = "INSERT INTO personne (nom_personne," + "prenom_personne,"
				+ " mail_personne," + " tel1_personne," + "tel2_personne,"
				+ "date_naissance_personne," + " adresse_personne,"
				+ " ville_personne, " + "code_postal_personne,"
				+ " login_personne," + " mot_de_passe_personne) VALUES ("
				+ "?,?,?,?,?,?,?,?,?,?,?)";

		String sql2 = "INSERT INTO administration(post_administration, id_personne) VALUES"
				+ "(?,?)";

		System.out.println(sql);
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);
			PreparedStatement stmt2 = connection.prepareStatement(sql2);
			
			stmt.setString(1, nomPersonne);
			stmt.setString(2, prenomPersonne);
			stmt.setString(3, mailPersonne);
			stmt.setString(4, tel1Personne);
			stmt.setString(5, tel2Personne);
			stmt.setDate(6, dateNaissanceSql);
			stmt.setString(7, adressePersonne);
			stmt.setString(8, villePersonne);
			stmt.setInt(9, codePostal);
			stmt.setString(10, login);
			stmt.setString(11, password);

			stmt.executeUpdate();

			ResultSet result = stmt.getGeneratedKeys();
			result.next();
			int idPersonne = result.getInt(1);

			stmt2.setString(1, posteAdmin);
			stmt2.setInt(2, idPersonne);

			stmt2.executeUpdate();

			return idPersonne;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}

	}

	public static Administrateur selectUnAdministrateurById(int idPersonne) {

		String sql = "SELECT * FROM personne p INNER JOIN administration a ON p.id_personne=a.id_personne WHERE p.id_personne=?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.setInt(1, idPersonne);

			ResultSet result = stmt.executeQuery();

			result.next();

			Administrateur unAdmin = new Administrateur();
			unAdmin.setIdPersonne(idPersonne);
			unAdmin.setNom(result.getString("nom_personne"));
			unAdmin.setPrenom(result.getString("prenom_personne"));
			unAdmin.setMail(result.getString("mail_personne"));
			unAdmin.setNumTel1(result.getString("tel1_personne"));
			unAdmin.setNumtel2(result.getString("tel2_personne"));
			unAdmin.setDateNaissance(result.getDate("date_naissance_personne"));
			unAdmin.setAdresse(result.getString("adresse_personne"));
			unAdmin.setVille(result.getString("ville_personne"));
			unAdmin.setCodePostale(result.getInt("code_postal_personne"));
			unAdmin.setLogin(result.getString("login_personne"));
			unAdmin.setPassword(result.getString("mot_de_passe_personne"));

			return unAdmin;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}

	public static ArrayList<Administrateur> selectAllAdministrateur() {
		ArrayList<Administrateur> allAdmin = new ArrayList<Administrateur>();
		String sql = "SELECT * FROM personne p INNER JOIN administration a ON p.id_personne = a.id_personne";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet result = stmt.executeQuery();

			while (result.next()) {
				Administrateur admin = new Administrateur();

				admin.setIdPersonne(result.getInt("id_personne"));
				admin.setNom(result.getString("nom_personne"));
				admin.setPrenom(result.getString("prenom_personne"));
				admin.setMail(result.getString("mail_personne"));
				admin.setNumTel1(result.getString("tel1_personne"));
				admin.setNumtel2(result.getString("tel2_personne"));
				admin.setDateNaissance(result
						.getDate("date_naissance_personne"));
				admin.setAdresse(result.getString("adresse_personne"));
				admin.setVille(result.getString("ville_personne"));
				admin.setCodePostale(result.getInt("code_postal_personne"));
				admin.setLogin(result.getString("login_personne"));
				admin.setPassword(result.getString("mot_de_passe_personne"));

				admin.setFonction(result.getString("post_administration"));

				allAdmin.add(admin);

			}

			return allAdmin;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;

		}

	}

	public static Administrateur deleteUnAdmin(int idAdmin) {

		String deleteA = "DELETE first, second FROM `administration` AS first INNER JOIN `personne` AS second WHERE first.id_personne = second.id_personne and id_administration='"
				+ idAdmin + "'";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(deleteA);
			stmt.executeUpdate(deleteA);
			System.out.println("c'est effacer dans les deux tables");

		} catch (SQLException e) {
			System.out.println(e);
		}
		return null;

	}

	public static Boolean updateUnAdminstrateur(Administrateur administrateur) {

		String update = "UPDATE personne  AS A INNER JOIN administration AS B ON A.id_personne=B.id_personne SET A.nom_personne=?,"
				+ "A.prenom_personne=?,"
				+ "A.mail_personne=?,"
				+ "A.tel1_personne=?,"
				+ "A.tel2_personne=?,"
				+ "A.date_naissance_personne=?,"
				+ "A.adresse_personne=?,"
				+ "A.ville_personne=?,"
				+ "A.code_postal_personne=?,"
				+ "A.login_personne=?,"
				+ "A.mot_de_passe_personne=?,"
				+ "B.post_administration =?" + " WHERE A.id_personne =?";
		

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(update);

			stmt.setString(1, administrateur.getNom());
			stmt.setString(2, administrateur.getPrenom());
			stmt.setString(3, administrateur.getMail());
			stmt.setString(4, administrateur.getNumTel1());
			stmt.setString(5, administrateur.getNumtel2());
			Date dateNaissance = administrateur.getDateNaissance();
			java.sql.Date dateNaissanceSql = new java.sql.Date(
					dateNaissance.getTime());
			stmt.setDate(6, dateNaissanceSql);
			stmt.setString(7, administrateur.getAdresse());
			stmt.setString(8, administrateur.getVille());
			stmt.setInt(9, administrateur.getCodePostale());
			stmt.setString(10, administrateur.getLogin());
			stmt.setString(11, administrateur.getPassword());
			stmt.setInt(13, administrateur.getIdPersonne());
			stmt.setString(12, administrateur.getFonction());

			stmt.executeUpdate();

			System.out.println("update effectué");

			return true;

		} catch (SQLException e) {
			System.out.println(e);

		}
		return null;
	}

	public static Administrateur selectUnAdminByLoginPassword(String login, String password){
		
		Administrateur unAdmin = new Administrateur();
		
		String select ="SELECT * FROM personne INNER JOIN administration ON personne.id_personne=administration.id_personne WHERE login_personne=? AND mot_de_passe_personne=?";
		
		Connection connection;
		try {
			connection = Bdd.getConnection();
			PreparedStatement stmt= connection.prepareStatement(select);
			
			stmt.setString(1, login);
			stmt.setString(2, password);
			
			ResultSet result = stmt.executeQuery();
			
			result.next();
			
			unAdmin.setIdPersonne(result.getInt("id_personne"));
			unAdmin.setNom(result.getString("nom_personne"));
			unAdmin.setPrenom(result.getString("prenom_personne"));
			unAdmin.setMail(result.getString("mail_personne"));
			unAdmin.setNumTel1(result.getString("tel1_personne"));
			unAdmin.setNumtel2(result.getString("tel2_personne"));
			unAdmin.setDateNaissance(result.getDate("date_naissance_personne"));
			unAdmin.setAdresse(result.getString("adresse_personne"));
			unAdmin.setVille(result.getString("ville_personne"));
			unAdmin.setCodePostale(result.getInt("code_postal_personne"));
			unAdmin.setLogin(login);
			unAdmin.setPassword(password);
			unAdmin.setFonction(result.getString("post_administration"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
	
		return unAdmin;
		}
	
	
}
